USE [CaseCentre]
GO

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'Admin' ) 

BEGIN
EXEC sp_executesql N'CREATE SCHEMA Admin AUTHORIZATION [dbo]'
END
GO

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'CaseCentre' ) 

BEGIN
EXEC sp_executesql N'CREATE SCHEMA CaseCentre AUTHORIZATION [dbo]'
END

GO

CREATE TABLE [Admin].[Controls] (
    [ID]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    UNIQUE NONCLUSTERED ([Name] ASC)
);

CREATE TABLE [Admin].[DataSource] (
    [ID]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [Admin].[DataValueSource] (
    [ID]           INT          IDENTITY (1, 1) NOT NULL,
    [DataSourceID] INT          NULL,
    [Name]         VARCHAR (50) NOT NULL,
    [Value]        VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    FOREIGN KEY ([DataSourceID]) REFERENCES [Admin].[DataSource] ([ID])
);

CREATE TABLE [Admin].[Form] (
    [ID]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [Admin].[FormDesign] (
    [ID]           INT          IDENTITY (1, 1) NOT NULL,
    [FormID]       INT          NULL,
    [ControlType]  VARCHAR (50) NULL,
    [ControlName]  VARCHAR (50) NULL,
    [LabelText]    VARCHAR (50) NULL,
    [DisplayOrder] INT          NULL,
    [DataSourceID] INT          NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    FOREIGN KEY ([ControlType]) REFERENCES [Admin].[Controls] ([Name]),
    FOREIGN KEY ([DataSourceID]) REFERENCES [Admin].[DataSource] ([ID]),
    FOREIGN KEY ([FormID]) REFERENCES [Admin].[Form] ([ID])
);


CREATE TABLE [Admin].[FormAnswer] (
    [ID]           INT          IDENTITY (1, 1) NOT NULL,
    [FormDetailID] INT          NULL,
    [Answer]       VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    FOREIGN KEY ([FormDetailID]) REFERENCES [Admin].[FormDesign] ([ID])
);


----------------------------- Case Centre ------------------------
CREATE TABLE [CaseCentre].[Country] (
    [ID]   INT          IDENTITY (1, 1) NOT NULL,
    [Code] VARCHAR (10) NULL,
    [Name] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[Address] (
    [ID]           INT           IDENTITY (1, 1) NOT NULL,
    [CountryID]    INT           NULL,
    [State]        VARCHAR (50)  NULL,
    [City]         VARCHAR (50)  NULL,
    [Address1]     VARCHAR (50)  NULL,
    [Address2]     VARCHAR (50)  NULL,
    [PostalCode]   VARCHAR (10)  NULL,
    [CreatedOnUtc] DATETIME2 (7) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Address_Country] FOREIGN KEY ([CountryID]) REFERENCES [CaseCentre].[Country] ([ID])
);

CREATE TABLE [CaseCentre].[Business] (
    [ID]                  INT           IDENTITY (1, 1) NOT NULL,
    [BusinessName]        VARCHAR (100) NULL,
    [Email]               VARCHAR (50)  NULL,
    [AdminComment]        VARCHAR (200) NULL,
    [CreatedOnUtc]        DATETIME2 (7) NULL,
    [LastActivityDateUtc] DATETIME2 (7) NULL,
    [PhoneNumber]         VARCHAR (20)  NULL,
    [FaxNumber]           VARCHAR (20)  NULL,
    [AddressID]           INT           NULL,
    [LeadContactId]       INT           NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC));

CREATE TABLE [CaseCentre].[BusinessOffer] (
    [ID]             INT          IDENTITY (1, 1) NOT NULL,
    [OfferType]      INT NULL,
    [OfferRangeCode] VARCHAR (20) NULL,
    [DistressAmount] DECIMAL (10) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [Unique_BusinessOffer] UNIQUE NONCLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[ComplaintType]
(
	 ID INT PRIMARY KEY IDENTITY(1,1),
	 Code VARCHAR(50),
	 Description NVARCHAR(100)
);

CREATE TABLE [CaseCentre].[Consumer] (
    [ID]                  INT           IDENTITY (1, 1) NOT NULL,
    [Title]               VARCHAR (5)   NULL,
    [FirstName]           VARCHAR (30)  NULL,
    [MiddleName]          VARCHAR (30)  NULL,
    [LastName]            VARCHAR (30)  NULL,
    [BirthDate]           DATETIME      NULL,
    [Email]               VARCHAR (50)  NULL,
    [PhoneNumber]         VARCHAR (20)  NULL,
    [FaxNumber]           VARCHAR (20)  NULL,
    [AdminComment]        VARCHAR (200) NULL,
    [CreatedOnUtc]        DATETIME      NULL,
    [LastActivityDateUtc] DATETIME2 (7) NULL,
    [AddressID]           INT           NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Consumer_Address] FOREIGN KEY ([AddressID]) REFERENCES [CaseCentre].[Address] ([ID])
);

CREATE TABLE [CaseCentre].[Team] (
    [ID]                 INT           IDENTITY (1, 1) NOT NULL,
    [Name]               VARCHAR (20)  NULL,
    [ManagerID]          INT           NULL,
    [ProductID]          INT           NULL,
    [Description]        VARCHAR (100) NULL,
    [TeamCode]           VARCHAR (20)  NULL,
    [TeamCreationReason] VARCHAR (50)  NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[UserProfile] (
    [ID]                INT            IDENTITY (1, 1) NOT NULL,
    [BusinessID]        INT            NULL,
    [IsBusinessAdmin]   BIT            NULL,
    [UserPosition]      VARCHAR (20)   NULL,
    [PhoneNumber]       VARCHAR (20)   NULL,
    [FaxNumber]         VARCHAR (20)   NULL,
    [Email]             VARCHAR (50)   NULL,
    [Title]             VARCHAR (10)   NULL,
    [Password]          VARCHAR (50)   NULL,
    [IsLogOn]           BIT            NULL,
    [LogOffDate]        DATETIME2 (7)  NULL,
    [LogOnAttempt]      INT            NULL,
    [FirstName]         VARCHAR (30)   NULL,
    [LastName]          VARCHAR (30)   NULL,
    [LockOutExpireDate] DATETIME2 (7)  NULL,
    [PassExpireDate]    DATETIME2 (7)  NULL,
    [AccountExpireDate] DATETIME2 (7)  NULL,
    [MachineIP]         NVARCHAR (500) NULL,
    [Deleted]           BIT            NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_UserProfile_Business] FOREIGN KEY ([BusinessID]) REFERENCES [CaseCentre].[Business] ([ID])
);

CREATE TABLE [CaseCentre].[JuridictionReason] (
    [ID]          INT           IDENTITY (1, 1) NOT NULL,
    [Name]        VARCHAR (50)  NULL,
    [Description] VARCHAR (100) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[OfferRange] (
    [ID]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    [Code] VARCHAR (20) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[Pool] (
    [ID]           INT           IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (20)  NULL,
    [ProductID]    INT           NULL,
    [Description]  VARCHAR (50)  NULL,
    [CreatedOnUtc] DATETIME2 (7) NULL,
    [WorkPoolCode] VARCHAR (20)  NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[Product] (
    [ID]           INT           IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50)  NULL,
    [Description]  VARCHAR (100) NULL,
    [AdminComment] VARCHAR (100) NULL,
    [CreatedOnUtc] DATETIME2 (7) NULL,
    [UpdatedOnUtc] DATETIME2 (7) NULL,
    [Category]     VARCHAR (50)  NULL,
    [SubCategory]  VARCHAR (50)  NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [CaseCentre].[Resolution] (
    [ID]                    INT           IDENTITY (1, 1) NOT NULL,
    [Name]                  VARCHAR (50)  NULL,
    [ComplainID]            INT           NULL,
    [Description]           VARCHAR (50)  NULL,
    [FreeDataXML]           VARCHAR (500) NULL,
    [CreatedOnUtc]          DATETIME2 (7) NULL,
    [LastUpdatedOnUtc]      DATETIME2 (7) NULL,
    [OfferType]             INT  NULL,
    [DistressAmount]        DECIMAL (10)  NULL,
    [ResolutionRequestedBy] INT           NULL,
    [BusinessOfferID]       INT           NULL,
    [CreatedByID]           INT           NULL,
    [OfferRangeID]          INT           NULL,
    [IsOfferInline]         BIT           NULL,
    [NavigatorAssessmentID] INT           NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Resolution_OfferRange] FOREIGN KEY ([OfferRangeID]) REFERENCES [CaseCentre].[OfferRange] ([ID])
);

CREATE TABLE [CaseCentre].[Settlement] (
    [ID]               INT           IDENTITY (1, 1) NOT NULL,
    [Name]             VARCHAR (50)  NULL,
    [ComplainID]       INT           NULL,
    [Description]      VARCHAR (50)  NULL,
    [FreeDataXML]      VARCHAR (500) NULL,
    [CreatedOnUtc]     DATETIME2 (7) NULL,
    [LastUpdatedOnUtc] DATETIME2 (7) NULL,
    [OfferType]        INT  NULL,
    [DistressAmount]   DECIMAL (10)  NULL,
    [DecisionReqBy]    INT           NULL,
    [BusinessOfferID]  INT           NULL,
    [CreatedByID]      INT           NULL,
    [ResolutionID]     INT           NULL,
    [OfferRangeID]     INT           NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Settlement_BusinessOffer] FOREIGN KEY ([OfferRangeID]) REFERENCES [CaseCentre].[BusinessOffer] ([ID]),
    CONSTRAINT [FK_Settlement_OfferRange] FOREIGN KEY ([OfferRangeID]) REFERENCES [CaseCentre].[OfferRange] ([ID]),
    CONSTRAINT [FK_Settlement_Resolution] FOREIGN KEY ([ResolutionID]) REFERENCES [CaseCentre].[Resolution] ([ID])
);

CREATE TABLE [CaseCentre].[Complain] (
    [ID]                  INT           IDENTITY (1, 1) NOT NULL,
    [ConsumerID]          INT           NULL,
    [BusinessID]          INT           NULL,
    [ProductID]           INT           NULL,
    [CreatedOnUtc]        DATETIME2 (7) NULL,
    [LastActivityOnUtc]   DATETIME2 (7) NULL,
    [Description]         VARCHAR (500) NULL,
    [AdminComment]        VARCHAR (100) NULL,
    [Priority]            INT           NULL,
    [PoolID]              INT           NULL,
    [OwnerUserID]         INT           NULL,
    [TeamID]              INT           NULL,
    [ComplainRef]         INT           NULL,
    [ClosedFlag]          BIT           NULL,
    [ClosedDate]          DATETIME2 (7) NULL,
    [NonProgressFlag]     INT           NULL,
    [Stage]               INT           NULL,
    [JuridictionReasonID] INT           NULL,
    [ResolutionID]        INT           NULL,
    [SettlementID]        INT           NULL,
    [ComplaintTypeID]     INT           NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_CaseComplain_Business] FOREIGN KEY ([BusinessID]) REFERENCES [CaseCentre].[Business] ([ID]),
    CONSTRAINT [FK_CaseComplain_ComplaintType] FOREIGN KEY ([ComplaintTypeID]) REFERENCES [CaseCentre].[ComplaintType] ([ID]),
    CONSTRAINT [FK_CaseComplain_Consumer] FOREIGN KEY ([ConsumerID]) REFERENCES [CaseCentre].[Consumer] ([ID]),
    CONSTRAINT [FK_CaseComplain_JuridictionReason] FOREIGN KEY ([JuridictionReasonID]) REFERENCES [CaseCentre].[JuridictionReason] ([ID]),
    CONSTRAINT [FK_CaseComplain_Pool] FOREIGN KEY ([PoolID]) REFERENCES [CaseCentre].[Pool] ([ID]),
    CONSTRAINT [FK_CaseComplain_Product] FOREIGN KEY ([ProductID]) REFERENCES [CaseCentre].[Product] ([ID]),
    CONSTRAINT [FK_CaseComplain_Resolution] FOREIGN KEY ([ResolutionID]) REFERENCES [CaseCentre].[Resolution] ([ID]),
    CONSTRAINT [FK_CaseComplain_Settlement] FOREIGN KEY ([SettlementID]) REFERENCES [CaseCentre].[Settlement] ([ID]),
    CONSTRAINT [FK_CaseComplain_Team] FOREIGN KEY ([TeamID]) REFERENCES [CaseCentre].[Team] ([ID]),
    CONSTRAINT [FK_CaseComplain_UserProfile] FOREIGN KEY ([OwnerUserID]) REFERENCES [CaseCentre].[UserProfile] ([ID])
);